//--------------------------------------------------------------
//
// Qizhi Zhang 
// ezhung@gmail.com
// http://www.moorwind.com
//
//--------------------------------------------------------------
package reader.dao
{
	import __AS3__.vec.Vector;
	
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	
	import reader.model.vo.BlogDraftVO;
	import reader.model.vo.UserVO;
	
	public class DraftDAO extends BaseDAO implements IDraftDAO
	{
		//////////////////////////////////////////////////////////////////////////////
		//SQLStatement queries
		
		private static const INSERT_DRAFT:String = "INSERT INTO draft (title, description, userId, creatDate, creatTime) VALUES(:title, :description, :userId, :creatDate, :creatTime)";
		private static const UPDATE_DRAFT:String = "UPDATE draft SET title=:title, description=:description, creatDate=:creatDate, creatTime=:creatTime WHERE draftId=:draftId";
		private static const UPDATE_PUBED_DRAFT:String = "UPDATE draft SET title=:title, description=:description, creatDate=:creatDate, creatTime=:creatTime, pubDate=:pubDate, pubTime=:pubTime, updateDate=:updateDate, updateTime=:updateTime, platforms=:platforms, platformid=:platformid, link=:link, postid=:postid, isPubed=:isPubed WHERE draftId=:draftId";
		private static const PUBLISH_DRAFT:String = "INSERT INTO draft (title, description, creatDate, creatTime, pubDate, pubTime, updateDate, updateTime, platforms, platformid, link, postid, isPubed, userId, deleteAble, editAble) VALUES (:title, :description, :creatDate, :creatTime, :pubDate, :pubTime, :updateDate, :updateTime, :platforms, :platformid, :link, :postid, :isPubed, :userId, :deleteAble, :editAble)";
		private static const DELETE_DRAFT:String = "DELETE FROM draft WHERE draftId=:draftId";
		private static const DELETE_DRAFT_BY_USER:String = "DELETE FROM draft WHERE userId=:userId";
		private static const SELETE_DRAFT:String = "SELECT draftId, title, description, pubDate,creatDate,updateDate,platforms,platformid,link, postid, isPubed, deleteAble, editAble FROM draft WHERE draftId=:draftId";
		private static const SELETE_DRAFTS:String = "SELECT draftId, title, description, pubDate,creatDate,updateDate,platforms,platformid,link, postid, isPubed, deleteAble, editAble FROM draft WHERE userId=:userId AND isPubed=:isPubed LIMIT 20";
		private static const SELETE_DRAFTS_BY_PLATFORM:String = "SELECT draftId, title, description, pubDate,creatDate,updateDate,platforms,platformid,link, postid, isPubed, deleteAble, editAble FROM draft WHERE userId=:userId AND platformid=:platformid ";
		private static const SELETE_ALL_DRAFTS:String = "SELECT draftId, title, description, pubDate,creatDate,updateDate,platforms,platformid,link, postid, isPubed, deleteAble, editAble FROM draft WHERE userId=:userId";
		private static const SELECT_COUNT:String = "SELECT COUNT(draftId) FROM draft WHERE userId=:userId";
		private static const SELECT_COUNT_DRAFT:String = "SELECT COUNT(draftId) FROM draft WHERE userId=:userId AND isPubed=false";
		
		//////////////////////////////////////////////////////////////////////////////
		//constructor
		public function DraftDAO(connection:SQLConnection)
		{
			super(connection);
		}
		
		//////////////////////////////////////////////////////////////////////////////
		//methods
		
		//----------------------------------------------------------------------------
		//
		// INSERT
		//
		//----------------------------------------------------------------------------
		
		public function insertDraft(draft:BlogDraftVO, user:UserVO):int
		{
			var sqlResult:SQLResult = execute(INSERT_DRAFT, {title: draft.title, description: draft.description, creatDate: draft.creatDate, creatTime: draft.creatDate.getTime(), userId: user.userId});
            return int(sqlResult.lastInsertRowID);
		}
		
		public function publishDraft(draft:BlogDraftVO, user:UserVO):int
		{
			var obj:Object = {};			
			obj.title = draft.title;
			obj.description = draft.description;
			obj.creatDate = draft.creatDate;
			obj.creatTime = draft.creatDate.getTime();
			obj.pubDate = draft.pubDate;
			obj.pubTime = draft.pubDate.getTime();
			obj.updateDate = draft.updateDate;
			obj.updateTime = draft.updateDate.getTime();
			obj.platforms = draft.platforms;
			obj.platformid = draft.platformid;	
			obj.isPubed = draft.isPubed;
			obj.link = draft.link;
			obj.postid = draft.postid;
			obj.deleteAble = draft.deleteAble;
			obj.editAble = draft.editAble;
			obj.userId = user.userId;
            var sqlResult:SQLResult = execute(PUBLISH_DRAFT, obj);
            return int(sqlResult.lastInsertRowID);
		}
		
		//----------------------------------------------------------------------------
		//
		// UPDATE
		//
		//----------------------------------------------------------------------------
		
		public function updateDraft(draft:BlogDraftVO):Boolean
		{
			var obj:Object = {};			
			obj.title = draft.title;
			obj.description = draft.description;
			obj.creatDate = draft.creatDate;
			obj.creatTime = draft.creatDate.getTime();
			obj.draftId = draft.draftId;			
            return update(UPDATE_DRAFT, obj);
		}
		
		public function updatePublishedDraft(draft:BlogDraftVO):Boolean
		{
			var obj:Object = {};			
			obj.title = draft.title;
			obj.description = draft.description;
			obj.creatDate = draft.creatDate;
			obj.creatTime = draft.creatDate.getTime();
			obj.pubDate = draft.pubDate;
			obj.pubTime = draft.pubDate.getTime();
			obj.updateDate = draft.updateDate;
			obj.updateTime = draft.updateDate.getTime();
			obj.platforms = draft.platforms;
			obj.platformid = draft.platformid;	
			obj.isPubed = draft.isPubed;
			obj.link = draft.link;
			obj.postid = draft.postid;
			obj.draftId = draft.draftId;		
            return update(UPDATE_PUBED_DRAFT, obj);
		}
		
		//----------------------------------------------------------------------------
		//
		// DELETE
		//
		//----------------------------------------------------------------------------
		
		public function deleteDraft(draft:BlogDraftVO):Boolean
		{
            return drop(DELETE_DRAFT, {draftId: draft.draftId});
		}
		
		public function deleteDraftByUser(user:UserVO):Boolean
		{
            return drop(DELETE_DRAFT_BY_USER, {userId: user.userId});
		}
		
		public function deleteDrafts(drafts:Vector.<BlogDraftVO>):void
		{
			this.connection.begin();
            for each(var draft:BlogDraftVO in drafts)
            {
            	executeTransaction(DELETE_DRAFT, {draftId: draft.draftId});
            }
            
            try
            {
            	this.connection.commit();
            }
            catch(e:Error)
            {
            	trace(e);
            }
		}
		
		//----------------------------------------------------------------------------
		//
		// SELETE
		//
		//----------------------------------------------------------------------------
		
		public function selectDraft(draftid:int):BlogDraftVO
		{
			return select(SELETE_DRAFT, {draftId: draftid})[0];
		}
		
		public function selectUnPublishedDrafts(userid:int, prefetch:int = -1):Vector.<BlogDraftVO>
		{
			return select(SELETE_DRAFTS, {userId: userid, isPubed: false}, prefetch);
		}
		
		public function selectPublishedDrafts(userid:int, prefetch:int = -1):Vector.<BlogDraftVO>
		{
			return select(SELETE_DRAFTS, {userId: userid, isPubed: true}, prefetch);
		}
		
		public function selectAllDrafts(userid:int):Vector.<BlogDraftVO>
		{
			return select(SELETE_ALL_DRAFTS, {userId: userid});
		}
		
		private function select(query:String, data:Object, prefetch:int = -1):Vector.<BlogDraftVO>
		{
			var result:SQLResult = execute(query, data, prefetch);
			if(!result || !result.data)
			{
				return null;
			}
			
			var dataArray:Vector.<BlogDraftVO> = new Vector.<BlogDraftVO>();
			for each(var obj:Object in result.data)
			{
				var _data:BlogDraftVO = new BlogDraftVO();
				_data.draftId = obj.draftId;
				_data.pubDate = obj.pubDate;
				_data.creatDate = obj.creatDate;
				_data.updateDate = obj.updateDate;
				_data.platforms = obj.platforms;
				_data.platformid = obj.platformid;
				_data.link = obj.link;
				_data.postid = obj.postid;
				_data.description = obj.description;
				_data.title = obj.title;
				_data.isPubed = obj.isPubed;
				_data.deleteAble = obj.deleteAble;
				_data.editAble = obj.editAble;
				dataArray.push(_data);
			}
            return dataArray;	
			
		}
		
		
		//----------------------------------------------------------------------------
		//
		// COUNT
		//
		//----------------------------------------------------------------------------
		
		public function getCount(user:UserVO):uint
		{
			return count(SELECT_COUNT, {userId: user.userId});
		}
		
		public function getDraftCount(user:UserVO):uint
		{
			return count(SELECT_COUNT_DRAFT, {userId: user.userId});
		}
		
	}
}